package com.egao.common.system.biz.impl;

import com.egao.common.core.damon.constant.UserConstant;
import com.egao.common.core.damon.results.BaseDao;
import com.egao.common.core.damon.results.PageResult2;
import com.egao.common.core.damon.utils.DateUtil;
import com.egao.common.core.damon.utils.Utils;
import com.egao.common.core.web.JsonResult;
import com.egao.common.system.biz.UserBiz;
import com.egao.common.system.entity.Menu;
import com.egao.common.system.entity.Role;
import com.egao.common.system.entity.User;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * 数据层实现类
 * @author : Doman
 * @date : 2020-05-27 15:04:13
 * @version ：1.0
 */
@Repository
public class UserBizImpl extends BaseDao implements UserBiz {


	@Override
	public User getByXcxopenId(User user) {
		RowMapper<User> rm;
		rm = new BeanPropertyRowMapper<User>(User.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select u.user_id,u.organization_id");
		sql.append(",u.gzhopenid,u.xcxopenid,u.username");
		sql.append(",u.password,u.nick_name");
		sql.append(",u.country,u.province,u.city");
		sql.append(",u.avatar,u.sex");
		sql.append(",u.phone,u.email,u.email_verified,u.true_name");
		sql.append(",u.id_card,u.birthday,u.introduction");
		sql.append(",u.state,u.admin,u.create_time,u.update_time ");
		sql.append(" from sys_user u ");
		sql.append(" where u.deleted=0 and u.xcxopenid=:xcxopenid ");
		map.put("xcxopenid", user.getXcxopenid());
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}

	@Override
	public User getByPhone(User user) {
		RowMapper<User> rm;
		rm = new BeanPropertyRowMapper<User>(User.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select u.user_id,u.organization_id");
		sql.append(",u.gzhopenid,u.xcxopenid,u.username");
		sql.append(",u.password,u.nick_name");
		sql.append(",u.country,u.province,u.city");
		sql.append(",u.avatar,u.sex");
		sql.append(",u.phone,u.email,u.email_verified,u.true_name");
		sql.append(",u.id_card,u.birthday,u.introduction");
		sql.append(",u.state,u.admin,u.create_time,u.update_time ");
		sql.append(" from sys_user u ");
		sql.append(" where u.deleted=0 and u.phone=:phone ");
		map.put("phone", user.getPhone());
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}


	@Override
	public Integer save(User user) {
		GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		KeyHolder keyHolder=new GeneratedKeyHolder();
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("insert into sys_user (");
		sql.append("organization_id,xcxopenid,username,password");
		sql.append(",passwordorigin,nick_name");
		sql.append(",country,province,birthday");
		sql.append(",sex,city,avatar");
		sql.append(",phone,state");
		sql.append(") values ( ");
		sql.append(":organization_id,:xcxopenid,:username,:password");
		sql.append(",:passwordorigin,:nick_name");
		sql.append(",:country,:province,:birthday");
		sql.append(",:sex,:city,:avatar");
		sql.append(",:phone,:state");
		sql.append(")");
		map.put("organization_id", user.getOrganizationId() + "");
		map.put("xcxopenid", user.getXcxopenid());
		map.put("username", user.getUsername());
		map.put("password", user.getPassword());
		map.put("passwordorigin", user.getPasswordorigin());
		map.put("nick_name", user.getNickName());
		map.put("country", user.getCountry());
		map.put("province", user.getProvince());
		map.put("birthday", user.getBirthday());
		map.put("sex", user.getSex()+"");
		map.put("city", user.getCity());
		map.put("avatar", user.getAvatar());
		map.put("phone", user.getPhone());
		map.put("state", user.getState() + "");
		getMapParameter(sql, map);
		int retVal = this.jdbcTemplate.update(sql.toString(), new SqlParameterSource() {
			@Override
			public boolean hasValue(String paramName) {
				return map.containsKey(paramName);
			}
			@Override
			public Object getValue(String paramName) throws IllegalArgumentException {
				return map.get(paramName);
			}
		}, generatedKeyHolder);

		Integer id = generatedKeyHolder.getKey().intValue();
		return id;
	}

	@Override
	public boolean update(User user) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set ");
		sql.append("nick_name=:nick_name");
		sql.append(",country=:country,province=:province");
		sql.append(",sex=:sex,city=:city,avatar=:avatar");
		sql.append(",phone=:phone");
		sql.append(" where user_id=:user_id ");
		map.put("nick_name", user.getNickName());
		map.put("country", user.getCountry());
		map.put("province", user.getProvince());
		map.put("sex", user.getSex()+"");
		map.put("city", user.getCity());
		map.put("avatar", user.getAvatar());
		map.put("phone", user.getPhone());
		map.put("user_id", user.getUserId()+"");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public JsonResult list(PageResult2<T> page, User user, User loginUser) throws Exception {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<User> rm = new BeanPropertyRowMapper<User>(User.class);
		StringBuffer sql = new StringBuffer("select u.user_id,u.organization_id,u.username");
		sql.append(",u.customer_id");
		sql.append(",CASE u.type WHEN '司机' THEN (select d.name from chf_driver d where d.id=u.customer_id) ");
		sql.append(" WHEN '车队' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '货代' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '平台用户' THEN u.username ELSE u.username ");
		sql.append(" END as customerName ");
		sql.append(",u.nick_name,u.avatar,u.sex");
		sql.append(",u.phone,u.email,u.email_verified,u.true_name");
		sql.append(",u.id_card,u.birthday,u.introduction");
		sql.append(",u.type,u.state,u.admin,u.create_time,u.update_time ");
		sql.append(" from sys_user u ");
		sql.append(" where u.deleted=0 ");
		if(loginUser.getUserId() != UserConstant.ADMIN_ID){
			sql.append(" and u.organization_id=:tableOrganizationId ");
			map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
		}
		if(Utils.isNotEmpty(user)){

			if(Utils.isNotEmpty(user.getUsername())){
				sql.append(" and instr(u.username,:username)>0 ");
				map.put("username", user.getUsername());
			}
			if(Utils.isNotEmpty(user.getNickName())){
				sql.append(" and instr(u.nick_name,:nick_name)>0 ");
				map.put("nick_name", user.getNickName());
			}

			sql.append(" order by u.create_time desc");
		}
		return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
	}

	@Override
	public User apiGetByCustomerId(String id, String type) {
		RowMapper<User> rm;
		rm = new BeanPropertyRowMapper<User>(User.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select u.user_id,u.organization_id,u.username");
		sql.append(",u.customer_id");
		sql.append(",CASE u.type WHEN '司机' THEN (select d.name from chf_driver d where d.id=u.customer_id) ");
		sql.append(" WHEN '车队' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '货代' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '平台用户' THEN u.username ELSE u.username ");
		sql.append(" END as customerName ");
		sql.append(",u.password,u.nick_name,u.avatar,u.sex");
		sql.append(",u.phone,u.email,u.email_verified,u.true_name");
		sql.append(",u.id_card,u.birthday,u.introduction,u.admin");
		sql.append(",u.type,u.state,u.admin,u.create_time,u.update_time ");
		sql.append(" from sys_user u ");
		sql.append(" where u.deleted=0 and u.customer_id=:customer_id ");
		map.put("customer_id", id);
		sql.append(" and u.type=:type ");
		map.put("type", type);
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}
	@Override
	public User getUserById(User user) {
		RowMapper<User> rm = new BeanPropertyRowMapper<User>(User.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select u.user_id,u.organization_id,u.username");
		sql.append(",u.customer_id");
		sql.append(",CASE u.type WHEN '司机' THEN (select d.name from chf_driver d where d.id=u.customer_id) ");
		sql.append(" WHEN '车队' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '货代' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '平台用户' THEN u.username ELSE u.username ");
		sql.append(" END as customerName ");
		sql.append(",u.password,u.nick_name,u.avatar,u.sex");
		sql.append(",u.phone,u.email,u.email_verified,u.true_name");
		sql.append(",u.id_card,u.birthday,u.introduction,u.admin");
		sql.append(",u.type,u.admin,u.state,u.admin,u.create_time,u.update_time");
		sql.append(",sogzn.organization_name organizationName ");
		sql.append(" from sys_user u ");
		sql.append(" inner join sys_organization sogzn on sogzn.organization_id=u.organization_id ");
		sql.append(" where u.deleted=0 and sogzn.deleted=0 and u.user_id=:id ");
		map.put("id", user.getUserId()+"");
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}
	@Override
	public User getUserByPhone(User user) {
		RowMapper<User> rm = new BeanPropertyRowMapper<User>(User.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select u.user_id,u.organization_id,u.username");
		sql.append(",u.customer_id");
		sql.append(",CASE u.type WHEN '司机' THEN (select d.name from chf_driver d where d.id=u.customer_id) ");
		sql.append(" WHEN '车队' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '货代' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '平台用户' THEN u.username ELSE u.username ");
		sql.append(" END as customerName ");
		sql.append(",u.password,u.nick_name,u.avatar,u.sex");
		sql.append(",u.phone,u.email,u.email_verified,u.true_name");
		sql.append(",u.id_card,u.birthday,u.introduction,u.admin");
		sql.append(",u.type,u.admin,u.state,u.create_time,u.update_time");
		sql.append(",sogzn.organization_name organizationName ");
		sql.append(" from sys_user u ");
		sql.append(" inner join sys_organization sogzn on sogzn.organization_id=u.organization_id ");
		sql.append(" where u.deleted=0 and sogzn.deleted=0 and u.phone=:phone ");
		map.put("phone", user.getPhone()+"");
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}

	@Override
	public User getUserByUsername(User user) {
		RowMapper<User> rm;
		rm = new BeanPropertyRowMapper<User>(User.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select u.user_id,u.organization_id,u.username");
		sql.append(",u.customer_id");
		sql.append(",CASE u.type WHEN '司机' THEN (select d.name from chf_driver d where d.id=u.customer_id) ");
		sql.append(" WHEN '车队' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '货代' THEN (select c.name from chf_customer c where c.id=u.customer_id) ");
		sql.append(" WHEN '平台用户' THEN u.username ELSE u.username ");
		sql.append(" END as customerName ");
		sql.append(",u.password,u.nick_name,u.avatar,u.sex");
		sql.append(",u.phone,u.email,u.email_verified,u.true_name");
		sql.append(",u.id_card,u.birthday,u.introduction,u.admin");
		sql.append(",u.type,u.state,u.admin,u.create_time,u.update_time,u.deleted ");
		sql.append(",sogzn.organization_name organizationName ");
		sql.append(" from sys_user u ");
		sql.append(" inner join sys_organization sogzn on sogzn.organization_id=u.organization_id ");
		sql.append(" where u.deleted=0 and sogzn.deleted=0 and u.username=:username ");
		map.put("username", user.getUsername());
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}

	@Override
	public List<Role> getRoleIdsByUserId(User user) {
		RowMapper<Role> rm = new BeanPropertyRowMapper<Role>(Role.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select ");
		sql.append("r.role_Id,r.role_name ");
		sql.append(" from sys_user_role ur ");
		sql.append(" inner join sys_role r on r.role_Id=ur.role_Id ");
		sql.append(" where ur.user_id=:user_id ");
		map.put("user_id", user.getUserId()+"");
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(), map, rm);
	}

	@Override
	public List<Menu> apiGetMenuByUser(User user) {
		RowMapper<Menu> rm = new BeanPropertyRowMapper<Menu>(Menu.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("select authority ");
		sql.append("from sys_menu m,sys_role_menu rm,sys_user_role ur ");
		sql.append(" where rm.menu_id=m.menu_id and rm.role_id=ur.role_id ");
		sql.append(" and ur.user_id=:user_id ");
		map.put("user_id", user.getUserId()+"");
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(), map, rm);
	}

	@Override
	public User apiGetByUsername(String username) {
		RowMapper<User> rm;
		rm = new BeanPropertyRowMapper<User>(User.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select u.user_id,u.organization_id,u.username");
		sql.append(",u.password,u.nick_name,u.avatar,u.sex");
		sql.append(",u.phone,u.email,u.email_verified,u.true_name");
		sql.append(",u.id_card,u.birthday,u.introduction");
		sql.append(",u.type,u.state,u.admin,u.create_time,u.update_time ");
		sql.append(" from sys_user u ");
		sql.append(" where u.deleted=0 and u.username=:username ");
		map.put("username", username);
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}


	@Override
	public boolean saveUserRole(User user) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("insert into sys_user_role (");
		sql.append("user_id,role_id) values ( ");
		sql.append(":user_id,:role_id");
		sql.append(")");
		map.put("user_id", user.getUserId() + "");
		map.put("role_id", user.getRoleId() + "");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}
	@Override
	public boolean removeUserRole(User user) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("delete from sys_user_role ");
		sql.append("where user_id=:user_id and role_id=:role_id ");
		map.put("user_id", user.getUserId() + "");
		map.put("role_id", user.getRoleId() + "");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}
	@Override
	public boolean removeUserRoleAll(User user) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("delete from sys_user_role ");
		sql.append("where user_id=:user_id ");
		map.put("user_id", user.getUserId() + "");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}
	@Override
	public boolean apiExamine(User user, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set ");
		sql.append("state=:state ");
		sql.append(",update_user=:update_user where user_id=:user_id ");
		map.put("state", user.getState()+"");
		map.put("update_user", loginUser.getUserId() + "");
		map.put("user_id", user.getUserId()+"");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}
	@Override
	public boolean removeUser(User user, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set ");
		sql.append("deleted=1 ");
		sql.append(",update_user=:update_user where user_id=:user_id ");
		map.put("update_user", loginUser.getUserId() + "");
		map.put("user_id", user.getUserId()+"");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean updatePsw(User user, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set ");
		sql.append("password=:password,passwordorigin=:passwordorigin ");
		sql.append(",update_user=:update_user,update_time=now() where user_id=:user_id ");
		map.put("password", user.getPassword());
		map.put("passwordorigin", user.getPasswordorigin());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("user_id", user.getUserId()+"");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean apiExamineCustomerId(User user, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set ");
		sql.append("state=:state ");
		sql.append(",update_user=:update_user where customer_id=:customer_id ");
		map.put("state", user.getState()+"");
		map.put("update_user", loginUser.getUserId() + "");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean apiSetCustomerId(User user, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set ");
		sql.append("customer_id=:customer_id ");
		sql.append(" where user_id=:user_id ");
		map.put("user_id", user.getUserId()+"");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean updateUser(User user, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set ");
		sql.append("nick_name=:nick_name,phone=:phone ");
		sql.append(" where user_id=:user_id ");
		map.put("phone", user.getPhone());
		map.put("nick_name", user.getNickName());
		map.put("user_id", user.getUserId()+"");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}
}
